/*******************************************************************************
 * Package: com.song.kkxxpoi.word
 * Type:    ExcelText
 * Date:    2023-09-13 12:57
 *
 * Copyright (c) 2023 LTD All Rights Reserved.
 *
 * You may not use this file except in compliance with the License.
 *******************************************************************************/
package com.song.kkxxpoi.word;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * 功能描述：
 * 行去重 保留其中一个
 *
 * @author Songxianyang
 * @date 2023-09-13 12:57
 */
public class ExcelText {
    public static final String xlsx = ".xlsx";
    public static final String name = "石楼镇个体";

    public static final String fileName = "D:\\下载\\原始数据整理\\个体\\" + name + xlsx;
    public static final int handle = 5;
    public static final int total = 13;

    public static void main(String[] args) throws Exception {


        File file = new File(fileName);
        XSSFWorkbook xssfWorkbook;
        try {
            xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            throw e;
        }
        //获取第一张sheet
        Sheet sheet = xssfWorkbook.getSheetAt(0);
        //获取一个sheet有多少Row
        int rowSize = sheet.getPhysicalNumberOfRows();
        if (rowSize == 0) {
            return;
        }
        for (int i = 1, size = rowSize; i < size; i++) {
            Row row = sheet.getRow(i);
            //处理行
            handleRow(row);
        }
        // 将修改后的 Workbook 对象写入文件
        FileOutputStream out = new FileOutputStream(fileName);
        xssfWorkbook.write(out);
        out.close();
    }

    private static void handleRow(Row row) {
        List<Object> values = new ArrayList<>();
        for (int j = handle, size = total; j < size; j++) {
            if (!StringUtils.isEmpty(row.getCell(j))) {
                Cell cell = row.getCell(j);
                Object value = new Object();
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getRichStringCellValue().getString();
                        break;
                    case NUMERIC:
                        Double numericCellValue = cell.getNumericCellValue();
                        String sValue = numericCellValue.toString();
                        value = sValue;
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case FORMULA:
                        value = cell.getCellFormula();
                        break;
                    case BLANK:
                        value = null;
                        break;
                }
                if (StringUtils.isEmpty(value)) {
                    continue;
                }
                values.add(value);
            }
        }
        List<String> distinct = new ArrayList<>();
        for (int j = handle, size = total; j < size; j++) {
            if (!StringUtils.isEmpty(row.getCell(j))) {
                Cell cell = row.getCell(j);
                Object value = new Object();
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getRichStringCellValue().getString();
                        break;
                    case NUMERIC:
                        Double numericCellValue = cell.getNumericCellValue();
                        String sValue = numericCellValue.toString();
                        value = sValue;
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case FORMULA:
                        value = cell.getCellFormula();
                        break;
                    case BLANK:
                        value = null;
                        break;
                }
                if (StringUtils.isEmpty(value)) {
                    continue;
                }
                Object finalValue = value;
                if (values.stream().filter(s -> Objects.equals(s, finalValue.toString())).collect(Collectors.toList()).size() == 1) {
                    distinct.add(value.toString());
                    continue;
                } else {
                    if (!distinct.contains(value)) {
                        distinct.add(value.toString());
                        continue;
                    } else {
                        row.getCell(j).setCellValue("");
                        continue;
                    }
                }
            }
        }
    }


}
